package mudbconnection;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import mudbconnection.DBQuestionnaireSimpleSummaryService.QuestionnaireSimpleSummaryMapper;
import muobject.QuestionnaireSimpleSummary;
import muobject.StudentSummary;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

@Component("DBStudentSummaryService")
public class DBStudentSummaryService implements DBStudentSummary {

	private JdbcTemplate jdbct;
	
	@Autowired
	public DBStudentSummaryService(DataSource jdbct) {
		super();
		this.jdbct = new JdbcTemplate(jdbct);
	}
	
	public Boolean ExistDataTable(){
		int tableCount = jdbct.queryForInt("Select count(*) from TempStudentSummary");
		return tableCount>0 ? true : false;
	}
	
	public List<StudentSummary> getAllStudentSummary(){
		@SuppressWarnings("unchecked")
		List<StudentSummary> studentSummarys = (List<StudentSummary>)jdbct
				.query("Select * from TempStudentSummary",
				new StudentSummaryMapper());
		return studentSummarys;
	}
	
	public List<StudentSummary> getStudentSummaryFromOtherTable(String year, String stno){
		String sql = StudentSummaryOtherTableSQL(year, stno);
		try {
			@SuppressWarnings("unchecked")
			List<StudentSummary> studentSummarys = (List<StudentSummary>)jdbct
				.query(sql,
				new StudentSummaryOtherTableMapper());
			return studentSummarys;
		} catch (Exception e) {
			return null;
		}
	}
	
	public boolean setStudentSummary (StudentSummary studentSummary){
		try{
			jdbct.update("insert into TempStudentSummary(sno, shcname, shename, publictype, daytype, tel," +
					" fax, address, tname, ttel, temail, president, registerdate, stno, grade, sclass, classno, " +
					" ename, cname, sex, birthday, years, age, hwscore, gripscore, jumpscore, situpscore, vcapacityscore, " +
					" run69score, run15score, totalscore, plevel, testdate, height, weight, bMI, " +
					" standard, d1score, d2score, d3score, d4score, d5score, d6score, d7score, d8score, d9score, " +
					" d10score, d11score, d12score, d13score, d14score, d15score, dtotalscore, f1score, f2score, " +
					" f3score, f4score, f5score, f6score, f7score, f8score, f9score, f10score, ftotalscore, pcomment1, " +
					" pcomment2, pcomment3, pcomment4, dcomment1, dcomment2, dcomment3, dcomment4, total, award) " +
					" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," +
					"?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
				     new Object[] { studentSummary.getSno(), studentSummary.getShcname(), studentSummary.getShename(), 
					studentSummary.getPublictype(), studentSummary.getDaytype(), studentSummary.getTel(), studentSummary.getFax(), 
					studentSummary.getAddress(), studentSummary.getTname(), studentSummary.getTtel(), studentSummary.getTemail(),
					studentSummary.getPresident(), studentSummary.getRegisterdate(), studentSummary.getStno(), 
					studentSummary.getGrade(), studentSummary.getSclass(), studentSummary.getClassno(), 
					studentSummary.getEname(), studentSummary.getCname(), studentSummary.getSex(),studentSummary.getBirthday(),
					studentSummary.getYears(), studentSummary.getAge(), studentSummary.getHwscore(), studentSummary.getGripscore(),
					studentSummary.getJumpscore(), studentSummary.getSitupscore(), studentSummary.getVcapacityscore(),
					studentSummary.getRun69score(), studentSummary.getRun15score(), studentSummary.getTotalscore(),
					studentSummary.getPlevel(), studentSummary.getTestdate(), studentSummary.getHeight(), 
					studentSummary.getWeight(), studentSummary.getBMI(),
					studentSummary.getStandard(), studentSummary.getD1score(), studentSummary.getD2score(),
					studentSummary.getD3score(), studentSummary.getD4score(), studentSummary.getD5score(),
					studentSummary.getD6score(), studentSummary.getD7score(), studentSummary.getD8score(),
					studentSummary.getD9score(), studentSummary.getD10score(), studentSummary.getD11score(),
					studentSummary.getD12score(), studentSummary.getD13score(), studentSummary.getD14score(),
					studentSummary.getD15score(), studentSummary.getDtotalscore(), studentSummary.getF1score(),
					studentSummary.getF2score(), studentSummary.getF3score(), studentSummary.getF4score(),
					studentSummary.getF5score(), studentSummary.getF6score(), studentSummary.getF7score(),
					studentSummary.getF8score(), studentSummary.getF9score(), studentSummary.getF10score(),
					studentSummary.getFtotalscore(), studentSummary.getPcomment1(), studentSummary.getPcomment2(),
					studentSummary.getPcomment3(), studentSummary.getPcomment4(), studentSummary.getDcomment1(),
					studentSummary.getDcomment2(), studentSummary.getDcomment3(), studentSummary.getDcomment4(),
					studentSummary.getTotal(), studentSummary.getAward()
					});
			return true;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	public boolean deleteAllStudentSummary(){
		try{
			jdbct.update("delete from TempStudentSummary");
			return true;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	public String StudentSummaryOtherTableSQL(String year, String stno){
		String sql = "Select sch.scid as scid, sch.sno as sno, sch.cname as shcname, sch.ename as shename, " +
				"sch.publictype as publictype, sch.daytype as daytype, " +
				"sch.tel as tel, sch.fax as fax, sch.address as address, sch.tname as tname, " +
				"sch.ttel as ttel, sch.temail as temail, sch.president as president, " +
				"sch.registerdate as registerdate, stu.stno as stno, stu.grade as grade, stu.sclass as sclass, " +
				"stu.classno as classno, stu.ename as ename, stu.cname as cname, " +
				"stu.sex as sex, stu.birthday as birthday, stu.years as years, phys.hwscore as hwscore, " +
				"phys.gripscore as gripscore, phys.jumpscore as jumpscore, " +
				"phys.situpscore as situpscore, phys.vcapacityscore as vcapacityscore, phys.run69score as run69score, " +
				"phys.run15score as run15score, phys.totalscore as totalscore, " +
				"phys.plevel as plevel, hwb.testdate as testdate, hwb.height as height, hwb.weight as weight, " +
				"que.d1score as d1score,  que.d2score as d2score, " +
				"que.d3score as d3score, que.d4score as d4score, que.d5score as d5score, que.d6score as d6score, " +
				"que.d7score as d7score, que.d8score as d8score, " +
				"que.d9score as d9score, que.d10score as d10score, que.d11score as d11score, que.d12score as d12score, " +
				"que.d13score as d13score, que.d14score as d14score, " +
				"que.d15score as d15score, que.dtotalscore as dtotalscore, que.f1score as f1score, que.f2score as f2score, " +
				"que.f3score as f3score, que.f4score as f4score, " +
				"que.f5score as f5score, que.f6score as f6score, que.f7score as f7score, que.f8score as f8score, " +
				"que.f9score as f9score, que.f10score as f10score, " +
				"que.ftotalscore as ftotalscore " +
				"from (((schools sch right join students stu " +
				"on sch.sno = stu.sno) left join " +
				"(select stno, ROUND(avg(t.hwscore), 0) as hwscore, ROUND(avg(t.gripscore), 0) as gripscore, " +
				"ROUND(avg(t.jumpscore), 0) as jumpscore, ROUND(avg(t.situpscore), 0) as situpscore, " +
				"ROUND(avg(t.vcapacityscore), 0) as vcapacityscore, ROUND(avg(t.run69score), 0) as run69score, " +
				"ROUND(avg(t.run15score), 0) as run15score, ROUND(avg(t.totalscore), 0) as totalscore, " +
				"ROUND(avg(t.plevel), 0) as plevel " +
				"from TempPhysicalSimpleSummary t group by stno) phys on stu.stno = phys.stno) left join " +
				"(select phy1.stno, phy1.years, phy1.TestDate, phy2.Height, phy2.Weight from " +
				"(select stno, years, max(Physicals.TestDate) as testdate " +
				"from Physicals group by stno, years) phy1 left join " +
				"(select stno, testdate, Height, Weight from Physicals) phy2 " +
				"on phy1.stno = phy2.stno and phy1.testdate = phy2.testdate ) HWB on stu.stno = HWB.stno " +
				"and stu.years = HWB.years) " +
				"left join " +
				"(select stno , ROUND(avg(tempq.d1score), 0) as d1score, ROUND(avg(tempq.d2score), 0) as d2score, " +
				"ROUND(avg(tempq.d3score), 0) as d3score, ROUND(avg(tempq.d4score), 0) as d4score, " +
				"ROUND(avg(tempq.d5score), 0) as d5score, ROUND(avg(tempq.d6score), 0) as d6score, " +
				"ROUND(avg(tempq.d7score), 0) as d7score, ROUND(avg(tempq.d8score), 0) as d8score, " +
				"ROUND(avg(tempq.d9score), 0) as d9score, ROUND(avg(tempq.d10score), 0) as d10score, " +
				"ROUND(avg(tempq.d11score), 0) as d11score, ROUND(avg(tempq.d12score), 0) as d12score, " +
				"ROUND(avg(tempq.d13score), 0) as d13score, ROUND(avg(tempq.d14score), 0) as d14score, " +
				"ROUND(avg(tempq.d15score), 0) as d15score, ROUND(avg(tempq.dtotalscore), 0) as dtotalscore, " +
				"ROUND(avg(tempq.f1score), 0) as f1score, ROUND(avg(tempq.f2score), 0) as f2score, " +
				"ROUND(avg(tempq.f3score), 0) as f3score, ROUND(avg(tempq.f4score), 0) as f4score, " +
				"ROUND(avg(tempq.f5score), 0) as f5score, ROUND(avg(tempq.f6score), 0) as f6score, " +
				"ROUND(avg(tempq.f7score), 0) as f7score, ROUND(avg(tempq.f8score), 0) as f8score, " +
				"ROUND(avg(tempq.f9score), 0) as f9score, ROUND(avg(tempq.f10score), 0) as f10score, " +
				"ROUND(avg(tempq.ftotalscore), 0) as ftotalscore from TempQuestionnaireSimpleSummary tempq " +
				"group by stno) que on stu.stno = que.stno " +
				"where stu.years like '" + year + "' and  stu.stno like '" + stno + "' ";
		return sql;
	}
	
	class StudentSummaryOtherTableMapper implements RowMapper{
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException{
			StudentSummary studentSummary = null;
			try{
				studentSummary = new StudentSummary(rs.getString("sno"), rs.getString("shcname"), 
						rs.getString("shename"),
						rs.getString("publictype") , rs.getString("daytype"), rs.getString("tel"),
						rs.getString("fax"),
						rs.getString("address"), rs.getString("tname"), rs.getString("ttel"), 
						rs.getString("temail"),
						rs.getString("president"), rs.getString("registerdate"), rs.getString("stno"), 
						rs.getString("grade"),
						rs.getString("sclass"), rs.getInt("classno"), rs.getString("ename"), 
						rs.getString("cname"), rs.getString("sex"),
						rs.getDate("birthday"), rs.getString("years"), 0, 
						rs.getInt("hwscore"), rs.getInt("gripscore"),
						rs.getInt("jumpscore"), rs.getInt("situpscore"), rs.getInt("vcapacityscore"), 
						rs.getInt("run69score"),
						rs.getInt("run15score"), rs.getInt("totalscore"), rs.getInt("plevel"), 
						rs.getDate("testdate"),
						rs.getDouble("height"), rs.getDouble("weight"), 0, 
						"",
						rs.getInt("d1score"), rs.getInt("d2score"), rs.getInt("d3score"), 
						rs.getInt("d4score"), rs.getInt("d5score"),
						rs.getInt("d6score"), rs.getInt("d7score"), rs.getInt("d8score"), 
						rs.getInt("d9score"), rs.getInt("d10score"),
						rs.getInt("d11score"), rs.getInt("d12score"), rs.getInt("d13score"), 
						rs.getInt("d14score"),
						rs.getInt("d15score"), rs.getInt("dtotalscore"), rs.getInt("f1score"), 
						rs.getInt("f2score"),
						rs.getInt("f3score"), rs.getInt("f4score"), rs.getInt("f5score"), 
						rs.getInt("f6score"), rs.getInt("f7score"),
						rs.getInt("f8score"), rs.getInt("f9score"), rs.getInt("f10score"), 
						rs.getInt("ftotalscore"),
						"", "", "",
						"", "", "",
						"", "", 1, 
						"");
			}catch(Exception e){
				System.out.println(e.getMessage());
			}
			return studentSummary;
		}
	}
	
	public int getStudentSexScoreMaxMin(String sex, String item, String type){
		int count;
		try{
			count = jdbct.queryForInt("select " + type + "(" + item + ") from TempStudentSummary where " +
					" Sex like ? ", new Object[]{ sex });
		}catch(Exception e){
			count=0;
		}
		return count;
	}
	
	public int getStudentSexScoreCount(String sex, String item, double lower, double height){
		int count;
		try{
			if (lower != 0){
				count = jdbct.queryForInt("select count(" + item + ") from TempStudentSummary where " +
					" Sex like ? and " + item + " <> 0 and " + item + " > ? and " + item + " <= ? ", new Object[]{ sex, lower, height });
			}else{
				count = jdbct.queryForInt("select count(" + item + ") from TempStudentSummary where " +
						" Sex like ? and " + item + " > ? and " + item + " <= ? ", new Object[]{ sex, lower, height });
			}
			
		}catch(Exception e){
			count=0;
		}
		return count;
	}
	
	public double getStudentSexScoreAvg(String sex, String item){
		double count;
		try{
			count = (Double) jdbct.queryForObject("select avg(" + item + ") from TempStudentSummary where " +
					" Sex like ? ", new Object[]{ sex }, Double.class);
		}catch(Exception e){
			count=0;
		}
		return count;
	}
	
	class StudentSummaryMapper implements RowMapper{
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException{
			StudentSummary studentSummary = null;
			try{
				studentSummary = new StudentSummary(rs.getString("sno"), rs.getString("shcname"), 
						rs.getString("shename"),
						rs.getString("publictype") , rs.getString("daytype"), rs.getString("tel"),
						rs.getString("fax"),
						rs.getString("address"), rs.getString("tname"), rs.getString("ttel"), 
						rs.getString("temail"),
						rs.getString("president"), rs.getString("registerdate"), rs.getString("stno"), 
						rs.getString("grade"),
						rs.getString("sclass"), rs.getInt("classno"), rs.getString("ename"), 
						rs.getString("cname"), rs.getString("sex"),
						rs.getDate("birthday"), rs.getString("years"), rs.getInt("age"), 
						rs.getInt("hwscore"), rs.getInt("gripscore"),
						rs.getInt("jumpscore"), rs.getInt("situpscore"), rs.getInt("vcapacityscore"), 
						rs.getInt("run69score"),
						rs.getInt("run15score"), rs.getInt("totalscore"), rs.getInt("plevel"), 
						rs.getDate("testdate"),
						rs.getDouble("height"), rs.getDouble("weight"), rs.getDouble("BMI"), 
						rs.getString("standard"),
						rs.getInt("d1score"), rs.getInt("d2score"), rs.getInt("d3score"), 
						rs.getInt("d4score"), rs.getInt("d5score"),
						rs.getInt("d6score"), rs.getInt("d7score"), rs.getInt("d8score"), 
						rs.getInt("d9score"), rs.getInt("d10score"),
						rs.getInt("d11score"), rs.getInt("d12score"), rs.getInt("d13score"), 
						rs.getInt("d14score"),
						rs.getInt("d15score"), rs.getInt("dtotalscore"), rs.getInt("f1score"), 
						rs.getInt("f2score"),
						rs.getInt("f3score"), rs.getInt("f4score"), rs.getInt("f5score"), 
						rs.getInt("f6score"), rs.getInt("f7score"),
						rs.getInt("f8score"), rs.getInt("f9score"), rs.getInt("f10score"), 
						rs.getInt("ftotalscore"),
						rs.getString("pcomment1"), rs.getString("pcomment2"), rs.getString("pcomment3"),
						rs.getString("pcomment4"), rs.getString("dcomment1"), rs.getString("dcomment2"),
						rs.getString("dcomment3"), rs.getString("dcomment4"), rs.getInt("total"), 
						rs.getString("award"));
			}catch(Exception e){
				System.out.println(e.getMessage());
			}
			return studentSummary;
		}
	}

}
